package com.zbs.framework.swagger.utils.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.zbs.framework.swagger.utils.DateUtils;
import com.zbs.framework.swagger.utils.excel.abs.PoiAbstract;
import com.zbs.framework.swagger.utils.excel.listener.ExcelListener;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

@Slf4j
public class EasyExcelUtil {

    /**
     * 导出
     *
     * @param clazz            映射实体
     * @param list             导出实体数据
     * @param fileName         文件名，不带后缀
     * @param mergeColumnIndex 从第几列开始合并单元格
     * @param response         返回体
     * @param <T>              实体类型
     */
    public static <T> void exportExcel(Class<T> clazz, List<T> list, String fileName, int mergeColumnIndex, HttpServletResponse response) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        exportExcel(clazz, list, fileName, ints, response);
    }

    /**
     * @param clazz            映射实体
     * @param list             导出实体数据
     * @param fileName         文件名，不带后缀
     * @param mergeColumnIndex 从第几列开始合并单元格
     * @param response         返回体
     * @param <T>              实体类型
     * @param isAdaptive       是否自适应宽度 true 自适应 false 不自适应
     */
    public static <T> void exportExcel(Class<T> clazz, List<T> list, String fileName, int mergeColumnIndex, boolean isAdaptive, HttpServletResponse response) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        exportExcel(clazz, list, fileName, ints, isAdaptive, response);
    }

    /**
     * 导出
     *
     * @param head             表头
     * @param list             集合
     * @param fileName         文件名
     * @param mergeColumnIndex 从第几列开始合并单元格
     * @param response         返回体
     * @param <T>              实体类型
     */
    public static <T> void exportExcel(List<List<String>> head, List<T> list, String fileName, int mergeColumnIndex, HttpServletResponse response) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        exportExcel(head, list, fileName, ints, response);
    }

    public static <T> void exportExcel(List<List<String>> head, List<T> list, String fileName, int[] mergeColumnIndex, HttpServletResponse response) {
        exportExcel(head, list, fileName, mergeColumnIndex, false, response);
    }

    public static <T> void exportExcel(List<List<String>> head, List<T> list, String fileName, int[] mergeColumnIndex, boolean isAdaptive, HttpServletResponse response) {
        try {
            setResponse(list.size(), fileName, response);
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream())
                    .head(head)
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(0, mergeColumnIndex));
            if (isAdaptive) {
                excelWriterBuilder.registerWriteHandler(new CustomCellWriteHandler());
            }
            excelWriterBuilder
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(fileName)
                    .doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static <T> void exportExcel(Map<String, Object> map, List<T> list, String fileName, String templateName, int mergeColumnIndex, HttpServletResponse response) {
        ExcelWriter excelWriter;
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        try {
            setResponse(list.size(), fileName, response);
            excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new ExcelFillCellMergeStrategy(0, ints)).excelType(ExcelTypeEnum.XLSX).withTemplate(templateName).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            if (!CollectionUtils.isEmpty(list)) {
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                excelWriter.fill(list, fillConfig, writeSheet);
            }
            if (map != null && !map.isEmpty()) {
                excelWriter.fill(map, writeSheet);
            }
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static <T> void exportExcel(String path, Map<String, Object> map, List<T> list, String templateName, int mergeColumnIndex) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        exportExcel(path, map, list, templateName, ints);
    }

    public static <T> void exportExcel(String path, Map<String, Object> map, List<T> list, String templateName, int[] ints) {
        ExcelWriter excelWriter;
        excelWriter = EasyExcel.write(path).registerWriteHandler(new ExcelFillCellMergeStrategy(0, ints)).excelType(ExcelTypeEnum.XLSX).withTemplate(templateName).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        if (!CollectionUtils.isEmpty(list)) {
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(list, fillConfig, writeSheet);
        }
        if (map != null && !map.isEmpty()) {
            excelWriter.fill(map, writeSheet);
        }
        excelWriter.finish();
    }

    public static <T> void exportExcel(Map<String, Object> map, Map<String, List<T>> mapList, String fileName, String templateName, int mergeColumnIndex, HttpServletResponse response) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        exportExcel(map, mapList, fileName, templateName, ints, response);
    }

    public static <T> void exportExcel(Map<String, Object> map, Map<String, List<T>> mapList, String fileName, String templateName, int[] ints, HttpServletResponse response) {
        ExcelWriter excelWriter;
        try {
            setResponse(0, fileName, response);
            excelWriter = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new ExcelFillCellMergeStrategy(0, ints)).excelType(ExcelTypeEnum.XLSX).withTemplate(templateName).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            if (!CollectionUtils.isEmpty(mapList)) {
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                mapList.keySet().forEach(key -> excelWriter.fill(new FillWrapper(key, mapList.get(key)), fillConfig, writeSheet));
            }
            if (map != null && !map.isEmpty()) {
                excelWriter.fill(map, writeSheet);
            }
            excelWriter.finish();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static <T> void exportExcel(String path, Map<String, Object> map, Map<String, List<T>> mapList, String templateName, int mergeColumnIndex) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        exportExcel(path, map, mapList, templateName, ints);
    }

    public static <T> void exportExcel(String path, Map<String, Object> map, Map<String, List<T>> mapList, String templateName, int[] ints) {
        ExcelWriter excelWriter;
        excelWriter = EasyExcel.write(path).registerWriteHandler(new ExcelFillCellMergeStrategy(0, ints)).excelType(ExcelTypeEnum.XLSX).withTemplate(templateName).build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        if (!CollectionUtils.isEmpty(mapList)) {
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            mapList.keySet().forEach(key -> excelWriter.fill(new FillWrapper(key, mapList.get(key)), fillConfig, writeSheet));
        }
        if (map != null && !map.isEmpty()) {
            excelWriter.fill(map, writeSheet);
        }
        excelWriter.finish();
    }

    public static <T> void exportExcel(Map<String, Object> map, String fileName, String templateName, HttpServletResponse response) {
        exportExcel(map, new ArrayList<>(), fileName, templateName, 0, response);
    }

    public static <T> void exportExcel(Map<String, Object> map, List<T> list, String fileName, String templateName, HttpServletResponse response) {
        exportExcel(map, list, fileName, templateName, 0, response);
    }

    public static <T> void exportExcel(Class<T> clazz, List<T> list, String fileName, int[] mergeColumnIndex, HttpServletResponse response) {
        exportExcel(clazz, list, fileName, mergeColumnIndex, false, response);
    }

    public static <T> void exportExcel(Class<T> clazz, List<T> list, String fileName, int[] mergeColumnIndex, boolean isAdaptive, HttpServletResponse response) {
        try {
            setResponse(list.size(), fileName, response);
            ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), clazz)
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(0, mergeColumnIndex));
            if (isAdaptive) {
                excelWriterBuilder.registerWriteHandler(new CustomCellWriteHandler());
            }
            excelWriterBuilder
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet(fileName)
                    .doWrite(list);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static <T> void exportExcel(String path, Class<T> clazz, List<T> list) {
        exportExcel(path, clazz, list, 0, false);
    }

    public static <T> void exportExcel(String path, Class<T> clazz, List<T> list, int mergeColumnIndex, boolean isAdaptive) {
        int[] ints = IntStream
                .range(0, mergeColumnIndex)
                .toArray();
        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(path, clazz).registerWriteHandler(new ExcelFillCellMergeStrategy(0, ints));
        if (isAdaptive) {
            excelWriterBuilder.registerWriteHandler(new CustomCellWriteHandler());
        }
        excelWriterBuilder
                .excelType(ExcelTypeEnum.XLSX)
                .sheet()
                .doWrite(list);
    }

    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz) {
        return readExcel(file, clazz, 0, 1);
    }

    public static <T> List<T> readExcel(InputStream io, String originalFilename, Class<T> clazz) {
        return readExcel(io, originalFilename, clazz, 0, 1);
    }

    public static <T> List<T> readExcel(String path, Class<T> clazz) {
        return readExcel(path, clazz, 0, 1);
    }

    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        try {
            return readExcel(file.getInputStream(), file.getOriginalFilename(), clazz, sheetNo, headRowNumber);
        } catch (IOException e) {
            e.printStackTrace();
            log.error("读取模板错误：{}", e.getMessage());
            return null;
        }
    }

    public static <T> List<T> readExcel(InputStream io, String originalFilename, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        UploadDataListener<T> listener = new UploadDataListener<>(headRowNumber);
        ExcelReaderBuilder read = EasyExcel.read(io, clazz, listener);
        //获取文件名
        if (StringUtils.hasLength(originalFilename)) {
            //获取文件后缀名
            String fileNameSuffix = originalFilename.substring(originalFilename.lastIndexOf("."));
            if (fileNameSuffix.toLowerCase(Locale.ROOT).equals(".csv")) {
                read.excelType(ExcelTypeEnum.CSV);
            }
        }

        read.extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doReadSync();
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    public static <T> List<T> readExcel(String path, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
        UploadDataListener<T> listener = new UploadDataListener<>(headRowNumber);
        ExcelReaderBuilder read = EasyExcel.read(path, clazz, listener);
        //获取文件名
        if (StringUtils.hasLength(path)) {
            //获取文件后缀名
            String fileNameSuffix = path.substring(path.lastIndexOf("."));
            if (fileNameSuffix.toLowerCase(Locale.ROOT).equals(".csv")) {
                read.excelType(ExcelTypeEnum.CSV);
            }
        }
        read.extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doReadSync();
        List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
        if (CollectionUtils.isEmpty(extraMergeInfoList)) {
            return listener.getData();
        }
        return explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber);
    }

    public static List<Map<String, String>> readExcel(MultipartFile file, Integer sheetNo) {
        ExcelListener listener = new ExcelListener();
        try {
            String originalFilename = file.getOriginalFilename();
            ExcelReaderBuilder read = EasyExcel.read(file.getInputStream(), listener);
            //获取文件名
            if (StringUtils.hasLength(originalFilename)) {
                //获取文件后缀名
                String fileNameSuffix = originalFilename.substring(originalFilename.lastIndexOf("."));
                if (fileNameSuffix.toLowerCase(Locale.ROOT).equals(".csv")) {
                    read.excelType(ExcelTypeEnum.CSV);
                }
            }
            read.sheet(sheetNo).doReadSync();
        } catch (IOException e) {
            log.error("读取异常",e);
            return null;
        }
        return getMapList(listener);
    }

    public static List<Map<String, String>> readExcel(InputStream stream,String originalFilename, Integer sheetNo) {
        ExcelListener listener = new ExcelListener();
        EasyExcel.read(stream, listener).sheet(sheetNo).doReadSync();
        try {
            ExcelReaderBuilder read = EasyExcel.read(stream, listener);
            //获取文件名
            if (StringUtils.hasLength(originalFilename)) {
                //获取文件后缀名
                String fileNameSuffix = originalFilename.substring(originalFilename.lastIndexOf("."));
                if (fileNameSuffix.toLowerCase(Locale.ROOT).equals(".csv")) {
                    read.excelType(ExcelTypeEnum.CSV);
                }
            }
            read.sheet(sheetNo).doReadSync();
        } catch (Exception e) {
            log.error("读取异常",e);
            return null;
        }
        return getMapList(listener);
    }

    public static List<Map<String, String>> readExcel(String path, Integer sheetNo) {
        ExcelListener listener = new ExcelListener();
        EasyExcel.read(path, listener).sheet(sheetNo).doReadSync();
        return getMapList(listener);
    }

    private static List<Map<String, String>> getMapList(ExcelListener listener) {
        List<Map<Integer, Map<Integer, String>>> list = listener.getList();
        Map<Integer, String> headTitleMap = listener.getHeadTitleMap();
        List<Map<String, String>> mapList = new ArrayList<>();
        for (Map<Integer, Map<Integer, String>> integerMapMap : list) {
            integerMapMap.forEach((k, l) -> {
                Map<String, String> map = new HashMap<>();
                l.forEach((y, z) -> map.put(headTitleMap.get(y), z));
                mapList.add(map);
            });
        }
        return mapList;
    }


    public static List<List<String>> readExcel2Table(InputStream in, Integer sheetNo) throws Exception {
        XSSFWorkbook workbook; // 工作簿
        XSSFSheet sheet; // 工作表
        XSSFRow row; // 行
        XSSFCell cell; // 列
        workbook = new XSSFWorkbook(in);
        sheet = workbook.getSheetAt(sheetNo);
        List<List<String>> table = new ArrayList<>();
        List<String> rowValues = new ArrayList<>();
        short cellNum;
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 0; i < lastRowNum; i++) {
            row = sheet.getRow(i);
            cellNum = row.getLastCellNum();
            for (int j = 0; j < cellNum; j++) {
                cell = row.getCell(j);
                if (null != cell) {
                    cell.setCellType(CellType.STRING);
                    rowValues.add(cell.getStringCellValue().trim());
                }
            }
            table.add(rowValues);
            rowValues = new ArrayList<>();
        }
        return table;
    }


    public static List<String> readExcelTitle(InputStream in, Integer sheetNo) throws Exception {
        XSSFWorkbook workbook; // 工作簿
        XSSFSheet sheet; // 工作表
        XSSFRow row; // 行
        XSSFCell cell; // 列
        workbook = new XSSFWorkbook(in);
        sheet = workbook.getSheetAt(sheetNo);
        List<String> rowValues = new ArrayList<>();
        short cellNum;
        for (int i = 0; i < 1; i++) {
            row = sheet.getRow(i);
            cellNum = row.getLastCellNum();
            for (int j = 0; j < cellNum; j++) {
                cell = row.getCell(j);
                if (null != cell) {
                    cell.setCellType(CellType.STRING);
                    rowValues.add(cell.getStringCellValue().trim());
                }
            }
        }
        return rowValues;
    }

    public static void main(String[] args) throws Exception {
//        List<ExcelPhoneModel> excelPhoneModels = readExcel(new FileInputStream("C:\\Users\\86131\\Desktop\\94万.xlsx"), "94万.xlsx", ExcelPhoneModel.class);
//        System.err.println(excelPhoneModels.size());
    }

    public static <T> List<T> readExcel(MultipartFile file, Integer sheetNo, Class<?> clazz, Integer startRow, PoiAbstract poiAbstract) throws Exception {
        return readExcel(file.getInputStream(),sheetNo,clazz,startRow,poiAbstract);
    }

    public static <T> List<T> readExcel(InputStream in, Integer sheetNo, Class<?> clazz, Integer startRow, PoiAbstract poiAbstract) throws Exception {
        XSSFWorkbook workbook; // 工作簿
        XSSFSheet sheet; // 工作表
        XSSFRow row; // 行
        XSSFCell cell; // 列
        workbook = new XSSFWorkbook(in);
        sheet = workbook.getSheetAt(sheetNo);
        List<T> list = new ArrayList<>();
        for (int i = startRow; i < sheet.getLastRowNum(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                T o = (T) clazz.getDeclaredConstructor().newInstance();
                for (Field field : clazz.getDeclaredFields()) {
                    field.setAccessible(true);
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    if (annotation != null) {
                        try {
                            cell = row.getCell(annotation.index());
                            if (cell != null) {
                                cell.setCellType(CellType.STRING);
                                try {
                                    if (field.getType().equals(Integer.class)) {
                                        field.set(o, Integer.parseInt(cell.getStringCellValue()));
                                    } else if (field.getType().equals(Boolean.class)) {
                                        field.set(o, Boolean.parseBoolean(cell.getStringCellValue()));
                                    } else if (field.getType().equals(Long.class)) {
                                        field.set(o, Long.parseLong(cell.getStringCellValue()));
                                    } else {
                                        field.set(o, cell.getStringCellValue());
                                    }
                                } catch (IllegalArgumentException e) {
                                    field.set(o, null);
                                }
                            }
                        } catch (IllegalAccessException var11) {
                            throw new RuntimeException("解析数据时发生异常!");
                        }
                    }
                }
                if (poiAbstract == null) {
                    list.add(o);
                } else {
                    if (poiAbstract.run(o)) {
                        list.add(o);
                    }
                }
            }
        }
        return list;
    }

    public static <T> List<T> readExcel(MultipartFile file, Integer sheetNo, Class<?> clazz, Integer startRow, Integer endRow, PoiAbstract poiAbstract) throws Exception {
        return readExcel(file.getInputStream(),sheetNo,clazz,startRow,endRow,poiAbstract);
    }

    public static <T> List<T> readExcel(InputStream in, Integer sheetNo, Class<?> clazz, Integer startRow, Integer endRow, PoiAbstract poiAbstract) throws Exception {
        XSSFWorkbook workbook; // 工作簿
        XSSFSheet sheet; // 工作表
        XSSFRow row; // 行
        XSSFCell cell; // 列
        workbook = new XSSFWorkbook(in);
        sheet = workbook.getSheetAt(sheetNo);
        List<T> list = new ArrayList<>();
        for (int i = startRow; i < endRow; i++) {
            row = sheet.getRow(i);
            if (row != null) {
                T o = (T) clazz.getDeclaredConstructor().newInstance();
                for (Field field : clazz.getDeclaredFields()) {
                    field.setAccessible(true);
                    ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
                    if (annotation != null) {
                        try {
                            cell = row.getCell(annotation.index());
                            if (cell != null) {
                                cell.setCellType(CellType.STRING);
                                try {
                                    if (field.getType().equals(Integer.class)) {
                                        field.set(o, Integer.parseInt(cell.getStringCellValue()));
                                    } else if (field.getType().equals(Boolean.class)) {
                                        field.set(o, Boolean.parseBoolean(cell.getStringCellValue()));
                                    } else if (field.getType().equals(Long.class)) {
                                        field.set(o, Long.parseLong(cell.getStringCellValue()));
                                    } else {
                                        field.set(o, cell.getStringCellValue());
                                    }
                                } catch (IllegalArgumentException e) {
                                    field.set(o, null);
                                }
                            }
                        } catch (IllegalAccessException var11) {
                            throw new RuntimeException("解析数据时发生异常!");
                        }
                    }
                }
                if (poiAbstract == null) {
                    list.add(o);
                } else {
                    if (poiAbstract.run(o)) {
                        list.add(o);
                    }
                }
            }
        }
        return list;
    }

    /**
     * 处理合并单元格
     *
     * @param data               解析数据
     * @param extraMergeInfoList 合并单元格信息
     * @param headRowNumber      起始行
     * @return 填充好的解析数据
     */
    private static <T> List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
//        循环所有合并单元格信息
        extraMergeInfoList.forEach(cellExtra -> {
            int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
            int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
            int firstColumnIndex = cellExtra.getFirstColumnIndex();
            int lastColumnIndex = cellExtra.getLastColumnIndex();
            if (firstRowIndex <= data.size() && lastRowIndex <= data.size()) {
                //            获取初始值
                Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
//            设置值
                for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                    for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
                        setInitValueToList(initValue, i, j, data);
                    }
                }
            }
        });
        return data;
    }

    /**
     * 设置合并单元格的值
     *
     * @param filedValue  值
     * @param rowIndex    行
     * @param columnIndex 列
     * @param data        解析数据
     */
    private static <T> void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
        T object = data.get(rowIndex);

        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == columnIndex) {
                    try {
                        field.set(object, filedValue);
                        break;
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException("解析数据时发生异常!");
                    }
                }
            }
        }
    }


    /**
     * 获取合并单元格的初始值
     * rowIndex对应list的索引
     * columnIndex对应实体内的字段
     *
     * @param firstRowIndex    起始行
     * @param firstColumnIndex 起始列
     * @param data             列数据
     * @return 初始值
     */
    private static <T> Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
        Object filedValue = null;
        T object = data.get(firstRowIndex);
        for (Field field : object.getClass().getDeclaredFields()) {
            //提升反射性能，关闭安全检查
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                if (annotation.index() == firstColumnIndex) {
                    try {
                        filedValue = field.get(object);
                        break;
                    } catch (IllegalAccessException e) {
                        throw new RuntimeException("解析数据时发生异常!");
                    }
                }
            }
        }
        return filedValue;
    }

    public static HttpServletResponse download(String path, HttpServletResponse response, HttpServletRequest request) {
        try {
            // path是指欲下载的文件的路径。
            File file = new File(path);
            // 取得文件名。
            String filename = file.getName();
            // 取得文件的后缀名。
            String ext = filename.substring(filename.lastIndexOf(".") + 1).toUpperCase();
            // 以流的形式下载文件。
            InputStream fis = new BufferedInputStream(new FileInputStream(path));
            byte[] buffer = new byte[fis.available()];
            fis.read(buffer);
            fis.close();
            // 清空response
            response.reset();
            // 设置response的Header
            filename = URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
            response.addHeader("Content-Length", "" + file.length());
            response.addHeader("Access-Control-Allow-Origin", request.getHeader("origin"));
            response.addHeader("X-Frame-Options", "DENY");
            response.addHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
            response.addHeader("Access-Control-Max-Age", "3600");
            response.addHeader("Access-Control-Allow-Headers", "*");
            response.addHeader("Access-Control-Allow-Credentials", "true");
            OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
            response.setContentType("application/octet-stream");
            toClient.write(buffer);
            toClient.flush();
            toClient.close();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
        return response;
    }


    private static <T> void setResponse(int size, String fileName, HttpServletResponse response) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            //查询并写入Excel
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=" + fileName + System.currentTimeMillis() + ".xlsx");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }

    // 写入数据
    public static void poiWrite(String fileName, boolean isAdaptive, List<EasyModel> easyModelList, HttpServletResponse response) {
        poiWrite(fileName, isAdaptive, easyModelList, null, response);
    }

    // 写入数据
    public static void poiWrite(String path, String sheetName, boolean isAdaptive, List<EasyModel> easyModelList) {
        poiWrite(path, sheetName, isAdaptive, easyModelList, null);
    }

    // 写入数据
    public static void poiWrite(String fileName, boolean isAdaptive, List<EasyModel> easyModelList, EasyExcelStyle excelStyle, HttpServletResponse response) {
        setResponse(0, fileName, response);
        XSSFWorkbook workbook = getXSSFWorkbook(fileName, isAdaptive, easyModelList, excelStyle);
        //写入到具体的文件数据中
        try {
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 写入数据
    public static void poiWrite(String path, String sheetName, boolean isAdaptive, List<EasyModel> easyModelList, EasyExcelStyle excelStyle) {
        XSSFWorkbook workbook = getXSSFWorkbook(sheetName, isAdaptive, easyModelList, excelStyle);
        //写入到具体的文件数据中
        FileOutputStream xlsStream = null;
        try {
            xlsStream = new FileOutputStream(path);
            workbook.write(xlsStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    private static XSSFWorkbook getXSSFWorkbook(String sheetName, boolean isAdaptive, List<EasyModel> easyModelList, EasyExcelStyle excelStyle) {
        if (CollectionUtils.isEmpty(easyModelList)) {
            throw new RuntimeException("数据不能为空");
        }
        //新建 Excel工作簿对象
        XSSFWorkbook workbook = new XSSFWorkbook();
        //新建工作表
        XSSFSheet sheet = workbook.createSheet(sheetName);
        final Map<Integer, Integer> maxColumnWidthMap = new HashMap<>();
        CellStyle style = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        XSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 10);
        font.setColor(IndexedColors.BLACK.index);
        style.setFont(font);
        style.setWrapText(true);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        Map<String, CellStyle> styleMap = new HashMap<>();
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        if (excelStyle != null && !excelStyle.getStyleMap().isEmpty()) {
            styleMap = excelStyle.getStyleMap().entrySet().stream().collect(Collectors.toMap(Map.Entry::getKey, en2 -> {
                ExcelStyleModel model = en2.getValue();
                CellStyle cellStyle = workbook.createCellStyle();
                //设置水平对齐的样式为居中对齐;
                cellStyle.setAlignment(model.getAlignment() == null ? HorizontalAlignment.CENTER : model.getAlignment());
                XSSFFont hssfFont = workbook.createFont();
                hssfFont.setFontName(!StringUtils.hasLength(model.getFontName()) ? "微软雅黑" : model.getFontName());
                hssfFont.setFontHeightInPoints((short) (model.getFontSize() == null ? 10 : model.getFontSize()));
                hssfFont.setColor(model.getFontColor() == null ? IndexedColors.BLACK.index : model.getFontColor().index);
                if (model.getIsBoldWeight() != null && model.getIsBoldWeight()) {
                    hssfFont.setBold(true);//加粗
                }
                cellStyle.setFont(hssfFont);
                cellStyle.setWrapText(model.getIsWrapText() != null && model.getIsWrapText());
                if (model.getBackgroundColor() != null && model.getFillPatternType() != null) {
                    cellStyle.setFillForegroundColor(model.getBackgroundColor().getIndex());
                    cellStyle.setFillPattern(model.getFillPatternType());
                }
                if (model.getBorderBottom() != null) {
                    cellStyle.setBorderBottom(model.getBorderBottom());//下边框
                }
                if (model.getBorderLeft() != null) {
                    cellStyle.setBorderLeft(model.getBorderLeft());//左边框
                }
                if (model.getBorderRight() != null) {
                    cellStyle.setBorderRight(model.getBorderRight());//右边框
                }
                if (model.getBorderTop() != null) {
                    cellStyle.setBorderTop(model.getBorderTop());//右边框
                }

                //设置垂直对齐的样式为居中对齐;
                cellStyle.setVerticalAlignment(model.getVerticalAlignment() == null ? VerticalAlignment.CENTER : model.getVerticalAlignment());

                return cellStyle;
            }));
        }


        //建立表格的列，这样行列数据有了，就确定了一个单元格，可以写入数据了
        Map<String, CellStyle> finalStyleMap = styleMap;
        easyModelList.forEach(model -> {
            XSSFRow row = sheet.getRow(model.getFirstRow());
            int firstRow = model.getFirstRow();
            if (row == null) {
                row = sheet.createRow(firstRow);
            }
            if (model.getHeight() != null) {
                row.setHeight(Short.parseShort(model.getHeight().toString()));
            }
            XSSFCell cell = row.createCell(model.getFirstCol());
            if (model.getIsImage() != null && model.getIsImage() && (!ObjectUtils.isEmpty(model.getUrl()) || !ObjectUtils.isEmpty(model.getPath()))) {
                ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                BufferedImage bufferImg = null;
                try {
                    if (!ObjectUtils.isEmpty(model.getUrl())) {
                        bufferImg = ImageIO.read(new URL(model.getUrl()));
                    }

                    if (!ObjectUtils.isEmpty(model.getPath())) {
                        bufferImg = ImageIO.read(new File(model.getPath()));
                    }
                    ImageIO.write(Objects.requireNonNull(bufferImg), "jpg", byteArrayOut);
                    XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
                    int col1 = model.getFirstCol();
                    int row1 = model.getFirstRow();
                    int col2 = model.getLastCol() == null ? model.getFirstCol() : model.getLastCol();
                    int row2 = model.getLastRow() == null ? model.getFirstRow() : model.getLastRow();
                    XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 1023, 255, (short) col1, row1, (short) col2, row2);
                    drawingPatriarch.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
                } catch (IOException e) {
                    e.printStackTrace();
                }
            } else if (!CollectionUtils.isEmpty(model.getBoxList())) {
                //指定将下拉框添加至1-10行，0-0列。即第一列的第2到11行
                DataValidation dataValidation = createBox(dvHelper,model.getBoxList(), model.getFirstRow(), model.getFirstRow(), model.getFirstCol(), model.getFirstCol());
                if (dataValidation != null) {
                    sheet.addValidationData(dataValidation);
                }
            } else {
                Integer columnWidth = dataLength(model.getVal(), cell);
                if (columnWidth >= 0) {
                    if (columnWidth > 255) {
                        columnWidth = 255;
                    }
                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (model.getWidth() != null) {
                        sheet.setColumnWidth(cell.getColumnIndex(), model.getWidth() * 512);
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    } else {
                        if (isAdaptive && (maxColumnWidth == null || columnWidth > maxColumnWidth)) {
                            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                            sheet.setColumnWidth(cell.getColumnIndex(), (columnWidth * 217));
                        }
                    }
                }
                CellStyle cellStyle = finalStyleMap.get(model.getStyleKey());
                CellStyle newCellStyle;
                if (cellStyle != null) {
                    newCellStyle = cellStyle;
                } else {
                    newCellStyle = style;
                }
                if (model.isDefaultColumnStyle() && model.getFirstCol() != null) {
                    newCellStyle.setDataFormat(format.getFormat("@"));
                    Integer lastCol = model.getLastCol();
                    if (lastCol == null) {
                        lastCol = model.getFirstCol();
                    }
                    for (int i = model.getFirstCol(); i <= lastCol; i++) {
                        sheet.setDefaultColumnStyle(i, newCellStyle);
                    }
                }
                cell.setCellStyle(newCellStyle);
                if (model.getLastRow() != null && model.getFirstCol() != null && model.getLastCol() != null) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(model.getFirstRow(), model.getLastRow(), model.getFirstCol(), model.getLastCol());
                    sheet.addMergedRegionUnsafe(cellRangeAddress);
                    setBorderStyle(sheet, newCellStyle, cellRangeAddress);
                }
            }
        });
        return workbook;
    }

    /**
     * 设置合并单元格边框 - 线条
     */
    private static void setBorderStyle(Sheet sheet, CellStyle cellStyle, CellRangeAddress region) {
        // 合并单元格左边框样式
        if (cellStyle.getBorderLeft() != null && cellStyle.getBorderLeft() != BorderStyle.NONE) {
            RegionUtil.setBorderLeft(cellStyle.getBorderLeft(), region, sheet);
        }

        // 合并单元格上边框样式
        if (cellStyle.getBorderTop() != null && cellStyle.getBorderTop() != BorderStyle.NONE) {
            RegionUtil.setBorderTop(cellStyle.getBorderTop(), region, sheet);
        }
        // 合并单元格右边框样式
        if (cellStyle.getBorderRight() != null && cellStyle.getBorderRight() != BorderStyle.NONE) {
            RegionUtil.setBorderRight(cellStyle.getBorderRight(), region, sheet);
        }
        // 合并单元格下边框样式
        if (cellStyle.getBorderBottom() != null && cellStyle.getBorderBottom() != BorderStyle.NONE) {
            RegionUtil.setBorderBottom(cellStyle.getBorderBottom(), region, sheet);
        }
    }

    /**
     * excel导出，有码值的数据使用下拉框展示。
     *
     * @param dvHelper
     * @param boxList  码值集合
     * @param firstRow 插入下拉框开始行号
     * @param lastRow  插入下拉框结束行号
     * @param firstCol 插入下拉框开始列号
     * @param lastCol  插入下拉框结束行号
     * @return
     */
    private static DataValidation createBox(XSSFDataValidationHelper dvHelper, List<String> boxList, int firstRow, int lastRow, int firstCol, int lastCol) {
        DataValidation dataValidation = null;
        //设置下拉框
        if (!CollectionUtils.isEmpty(boxList)) {
            //指定0-9行，0-0列为下拉框
            CellRangeAddressList cas = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //创建下拉数据列
//            DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(boxList.toArray(new String[]{}));
            //将下拉数据放入下拉框
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(boxList.toArray(new String[]{}));
            dvHelper.createValidation(dvConstraint, cas);
        }
        return dataValidation;
    }

    private static Integer dataLength(Object val, Cell cell) {
        if (val instanceof Date) {
            cell.setCellValue(DateUtils.parse(val.toString()));
            return cell.getDateCellValue().toString().getBytes().length;
        } else if (val instanceof Double) {
            cell.setCellValue(Double.parseDouble(val.toString()));
            return cell.getDateCellValue().toString().getBytes().length;
        } else {
            cell.setCellValue(val == null ? "" : val.toString());
            return cell.getStringCellValue().getBytes().length;
        }
    }

}
